package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import forum.Forum;
import forum.Member;
import forum.Post;
import forum.Thread;

public class RequestParser {

	private Connection conn;

	public RequestParser(Connection conn) {
		super();
		this.conn = conn;
	}

	public ArrayList<Member> parseGetFriends(String username) {
		ArrayList<Member> ans = new ArrayList<Member>();

		try {
			Statement s = conn.createStatement();
			ResultSet r = s
					.executeQuery("SELECT users.username, firstname, lastname, role, email, gender"
							+ " FROM friendsofusers JOIN users ON users.username = friendsofusers.friendname"
							+ " WHERE friendsofusers.username = '"
							+ username
							+ "'");
			while (r.next()) {
				String uName = r.getString("users.username");
				String fName = r.getString("users.firstname");
				String lName = r.getString("users.lastname");
				String mail = r.getString("users.email");
				String g = r.getString("users.gender");
				Member m = new Member(uName, fName, lName, mail, g);
				ans.add(m);
			}
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
		return ans;
	}

	public int parseAddFriend(String username, String friend) {
		int ans = 1;
		if (username.equals(friend)) {
			return 0;
		}
		try {
			Statement s = conn.createStatement();
			int r1 = s.executeUpdate("INSERT INTO "
					+ "`forum`.`friendsofusers` (`username`, `friendname`) "
					+ "VALUES ('" + username + "', '" + friend + "')");
			int r2 = s.executeUpdate("INSERT INTO "
					+ "`forum`.`friendsofusers` (`username`, `friendname`) "
					+ "VALUES ('" + friend + "', '" + username + "')");
			ans = ans & r1;
			ans = ans & r2;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}

	public int parseRemoveFriend(String username, String friend) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("DELETE FROM `forum`.`friendsofusers` WHERE `username`='"
							+ username + "' and`friendname`='" + friend + "'");
			int r2 = s
					.executeUpdate("DELETE FROM `forum`.`friendsofusers` WHERE `username`='"
							+ friend + "' and`friendname`='" + username + "'");
			ans = ans & r1;
			ans = ans & r2;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}

	public int parseAddUser(String username, String firstName, String lastName,
			String password, String email, String gender) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("INSERT INTO `forum`.`users` (`username`, `firstname`,"
							+ " `lastname`, `password`, `role`, `email`, `gender`) VALUES "
							+ "('"
							+ username
							+ "', '"
							+ firstName
							+ "', '"
							+ lastName
							+ "', '"
							+ password
							+ "', 'normal', '"
							+ email + "', '" + gender + "');");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}

	public int parseRemoveUser(String username) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("DELETE FROM `forum`.`users` WHERE `username`='"
							+ username + "';");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public int parseLogin(String username, String password) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT username, password"
					+ " FROM users"
					+ " WHERE username = '"
					+ username
					+ "'");
			
			String originalPass = "";
			while (r.next()) {
				originalPass = r.getString("users.password");
			}
			if(!password.equals(originalPass)) {
				ans = 0;
			}
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public int parseIsModerator(String username) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT role"
					+ " FROM users"
					+ " WHERE username = '"
					+ username
					+ "'");
			
			String role = "";
			while (r.next()) {
				role = r.getString("users.role");
			}
			if(!role.equals("moderator")) {
				ans = 0;
			}
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public ArrayList<Forum> parseGetForums() {
		ArrayList<Forum> ans = new ArrayList<Forum>();

		try {
			Statement s = conn.createStatement();
			ResultSet r = s
					.executeQuery("SELECT * FROM forums");
			while (r.next()) {
				int id = Integer.parseInt(r.getString("id"));
				String subject = r.getString("subject");
				Forum f = new Forum(id, subject);
				ans.add(f);
			}
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
		return ans;
	}
	
	public int parseAddForum(String subject) {
		int ans = 1;
		int nextID = this.getNextID("forums");
		try {
			
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("INSERT INTO `forum`.`forums` " +
							"(`id`, `subject`) VALUES ("+(nextID+1)+", '"+subject+"');");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public int parseRemoveForum(int forumID) {
		int ans = 1;
		try {
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("DELETE FROM `forum`.`forums` WHERE id='"
							+ forumID + "';");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public ArrayList<Thread> parseGetThreads(int ForumID) {
		ArrayList<Thread> ans = new ArrayList<Thread>();

		try {
			Statement s = conn.createStatement();
			ResultSet r = s
					.executeQuery("SELECT * FROM posts"
							+ " WHERE forumID = '"
							+ ForumID
							+ "'");
			while (r.next()) {
				int id = Integer.parseInt(r.getString("posts.id"));
				String subject = r.getString("posts.subject");
				String content = r.getString("posts.content");
				String author = r.getString("posts.author");
				Thread th = new Thread(id, subject, content, author);
				ans.add(th);
			}
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
		return ans;
	}
	
	public int parseAddThread(String username, int ForumID,
			String subject, String content, String date) {
		int ans = 1;
		int nextID = this.getNextID("posts");
		try {
			Statement s = conn.createStatement();
			int r1 = s.executeUpdate("INSERT INTO `forum`.`posts` " +
					"(`id`, `subject`, `content`, `date`, `author`, `forumID`) " +
					"VALUES ("+nextID+", '"+subject+"', " +
							"'"+content+"', '"+date+"', '"+username+"', "+ForumID+");");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public int parseRemoveThread(int ThreadID) {
		int ans = 1;
		try {
			String fID = "";
			Statement sTmp = conn.createStatement();
			ResultSet rTmp = sTmp.executeQuery("SELECT forumID FROM" +
					"`posts` WHERE id = "+ThreadID+";");
			while (rTmp.next()) {
				fID = rTmp.getString("forumID");
			}
			// null value iff forumID = null which means it's a reply post (and not a thread)
			if (fID == null) {
				System.out.println("Error: Trying to remove a Thread which is not a root post");
				return 0;
			}
			
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("DELETE FROM `forum`.`posts` WHERE id='"
							+ ThreadID + "';");
			ans = ans & r1;
			
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public int parseAddReply(int PostIDreplyTo, String username,
			String subject, String content, String date) {
		//TODO force constraint about adding reply not to a thread
		int ans = 1;
		int nextID = this.getNextID("posts");
		try {
			Statement s = conn.createStatement();
			int r1 = s.executeUpdate("INSERT INTO `forum`.`posts` " +
					"(`id`, `subject`, `content`, `date`, `author`, `forumID`) " +
					"VALUES ("+nextID+", '"+subject+"', " +
							"'"+content+"', '"+date+"', '"+username+"', null);");
			int r2 = s.executeUpdate("INSERT INTO `forum`.`replies` (`postid`, `replyid`) " +
					"VALUES ("+PostIDreplyTo+", "+nextID+");");
			ans = ans & r1;
			ans = ans & r2;
		} catch (Exception e) {
			try {
				Statement s = conn.createStatement();
				s.executeUpdate("DELETE FROM `forum`.`posts` WHERE id='"
						+ nextID + "';");
				System.out.println("Couldn't add reply: post is not exist");
			} catch (Exception e2) {
			}
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	
	public int parseRemoveReply(int replyID) {
		int ans = 1;
		try {
			String fID = "";
			Statement sTmp = conn.createStatement();
			ResultSet rTmp = sTmp.executeQuery("SELECT forumID FROM" +
					"`posts` WHERE id = "+replyID+";");
			while (rTmp.next()) {
				fID = rTmp.getString("forumID");
			}
			// null value iff forumID = null which means it's a reply post (and not a thread)
			if (fID != null) {
				System.out.println("Error: Trying to remove a reply which is a root post");
				return 0;
			}
			
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("DELETE FROM `forum`.`posts` WHERE id='"
							+ replyID + "';");
			int r2 = s
					.executeUpdate("DELETE FROM `forum`.`replies` WHERE replyid='"
							+ replyID + "';");
			ans = ans & r1;
			ans = ans & r2;
			
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	public ArrayList<Post> parseGetThread(int threadID) {
		ArrayList<Post> ans = new ArrayList<Post>();
		
		try {
			Statement s = conn.createStatement();
			ResultSet r = s
			.executeQuery("SELECT * FROM posts, replies " +
					"WHERE posts.id = replies.replyid " +
					"And replies.postid = "+threadID+";");
			while (r.next()) {
				int id = Integer.parseInt(r.getString("posts.id"));
				String subject = r.getString("posts.subject");
				String content = r.getString("posts.content");
				String date = r.getString("posts.date");
				String author = r.getString("posts.author");
				Post p = new Post(id, subject, content, null, date, author);
				ans.add(p);
			}
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
		return ans;
	}
	
	public Post parseGetPost(int PostID) {
		Post p = null;
		try {
			Statement s = conn.createStatement();
			System.out.println("SELECT * FROM posts " +
							"WHERE id = '"+PostID+";");
			ResultSet r = s
					.executeQuery("SELECT * FROM posts " +
							"WHERE id = '"+PostID+"';");
			while (r.next()) {
				int id = Integer.parseInt(r.getString("posts.id"));
				String subject = r.getString("posts.subject");
				String content = r.getString("posts.content");
				String date = r.getString("posts.date");
				String author = r.getString("posts.author");
				p = new Post(id, subject, content, null, date, author);
			}
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
		return p;
	}
	
	public int parseEditPost(int postID, String content) {
		int ans = 1;
		try {
			
			Statement s = conn.createStatement();
			int r1 = s
					.executeUpdate("UPDATE `forum`.`posts` " +
							"SET `content`='"+content+"' " +
							"WHERE `id`='"+postID+"';");
			ans = ans & r1;
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return ans;
	}
	
	///////// PRIVATE FUNCTIONS /////////////
	
	private int getNextID(String table) {
		//get the highest ID
		int nextID = -1;
		try {
			Statement sTmp = conn.createStatement();
			ResultSet rTmp = sTmp.executeQuery("SELECT MAX(id) AS maxID " +
							"FROM `forum`.`"+table+"`;");
			while (rTmp.next()) {
				nextID = Integer.parseInt(rTmp.getString("maxID"));
			}
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return nextID+1;
	}
}
